package ac.snu.street;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class StreetDB {	
	static private final String TABLE = "post";
	static private final String COL_ID = "id";
	static private final String COL_TIME = "time";
	static private final String COL_LAT = "latitude";
	static private final String COL_LONG = "longitude";
	static private final String COL_TITLE = "title";
	static private final String COL_CONT = "content";
	
	Connection conn;
	
	public StreetDB() throws SQLException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("S: Error: cannot connect to DB");
			System.exit(0);
		}
		
		System.out.println("S: Connecting database...");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/StreetPost", "generic", "generic");
	}
	
	public void close() throws SQLException {
		conn.close();
	}
	
	public int put(String latitude, String longitude, String title, String contents) throws SQLException {
		String atts = "";
		String values = "";
		
		atts += COL_TIME;
		values += System.currentTimeMillis();
		
		atts += ", " + COL_LAT;
		values += ", " + latitude;
		
		atts += ", " + COL_LONG;
		values += ", " + longitude;
		
		if (title != null) {
			atts += ", " + COL_TITLE;
			values += ", '" + title + "'";
		}
		
		if (contents != null) {
			atts += ", " + COL_CONT;
			values += ", '" + contents + "'";
		}
		
		String query = "INSERT INTO " + TABLE + "(" + atts + ") VALUES(" + values + ");";
		System.out.println(query); // debug
		PreparedStatement stmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
		
		stmt.executeUpdate();
		
		ResultSet rset = stmt.getGeneratedKeys();
		int gkey = -1;
		while (rset.next()) {
		    gkey = rset.getInt(1);
		}
		
		stmt.close();
		
		return gkey;
	}
	
	public void update(String id, String contents) throws SQLException {
		String timeAtt = COL_TIME + " = " + System.currentTimeMillis();
		String contentsAtt = COL_CONT + " = '" + contents + "'";
		String condId = COL_ID + " = " + id;
		
		String query = "UPDATE " + TABLE + " SET " + timeAtt + ", " + contentsAtt + " WHERE " + condId + ";";
		
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(query);
		
		stmt.close();
	}
	
	public void delete(String id) throws SQLException {
		String query = "DELETE FROM " + TABLE + " WHERE ID = " + id + ";";
		
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(query);
		
		stmt.close();
	}
	
	public List<String> searchByLocation(String latitude, String longitude, String range) throws SQLException {
		int d = Integer.parseInt(range);		
		int lat = Integer.parseInt(latitude);
		int lon = Integer.parseInt(longitude);
		
		String latCond = COL_LAT + " BETWEEN " + (lat - d) + " AND " + (lat + d);
		String longCond = COL_LONG + " BETWEEN " + (lon - d) + " AND " + (lon + d);
		String query = "SELECT * FROM " + TABLE + " WHERE " + latCond + " AND " + longCond + ";";
		System.out.println(query); // debug

		return getReulstFromQuery(query);
	}

	public List<String> searchById(String[] ids) throws SQLException {
		if (ids.length == 0) {
			return Collections.emptyList();
		}
		
		String query = "SELECT * FROM " + TABLE + " WHERE ID = " + ids[0];
		for (int i = 0; i < ids.length; i++) {
			query += " OR ID = " + ids[i];
		}
		
		return getReulstFromQuery(query);
	}
	
	private List<String> getReulstFromQuery(String query) throws SQLException {
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(query);

		List<String> ret = new ArrayList<String>();
		String note;
		while(rset.next()) {
			note = String.valueOf(rset.getInt(COL_ID));
			note += "\t" + String.valueOf(rset.getLong(COL_TIME));
			note += "\t" + String.valueOf(rset.getInt(COL_LAT));
			note += "\t" + String.valueOf(rset.getInt(COL_LONG));
			note += "\t" + rset.getString(COL_TITLE);
			note += "\t" + rset.getString(COL_CONT);
			
			System.out.println(note); // debug
			ret.add(note);
		}
		
		stmt.close();
	
		return ret;
	}
}
